const { DetectionText, EvaluationRecord, User } = require('./model');
const { Op } = require('sequelize');

class DetectionDao {
  // 检测文本相关操作
  
  // 创建检测文本
  async createDetectionText(textData) {
    try {
      return await DetectionText.create(textData);
    } catch (error) {
      throw new Error(`创建检测文本失败: ${error.message}`);
    }
  }

  // 获取检测文本列表
  async getDetectionTexts(options = {}) {
    try {
      const { page = 1, limit = 10, difficulty, category, keyword } = options;
      const offset = (page - 1) * limit;
      
      const whereClause = { is_active: true };
      if (difficulty) whereClause.difficulty = difficulty;
      if (category) whereClause.category = category;
      if (keyword) {
        whereClause[Op.or] = [
          { text: { [Op.like]: `%${keyword}%` } },
          { pinyin: { [Op.like]: `%${keyword}%` } }
        ];
      }

      const { count, rows } = await DetectionText.findAndCountAll({
        where: whereClause,
        order: [['usage_count', 'ASC'], ['created_at', 'DESC']],
        limit: parseInt(limit),
        offset: parseInt(offset)
      });

      return {
        texts: rows,
        total: count,
        page: parseInt(page),
        limit: parseInt(limit),
        totalPages: Math.ceil(count / limit)
      };
    } catch (error) {
      throw new Error(`获取检测文本列表失败: ${error.message}`);
    }
  }

  // 随机获取检测文本
  async getRandomDetectionText(difficulty = null, category = null) {
    try {
      const whereClause = { is_active: true };
      if (difficulty) whereClause.difficulty = difficulty;
      if (category) whereClause.category = category;

      const texts = await DetectionText.findAll({
        where: whereClause,
        order: DetectionText.sequelize.random(),
        limit: 1
      });

      return texts.length > 0 ? texts[0] : null;
    } catch (error) {
      throw new Error(`获取随机检测文本失败: ${error.message}`);
    }
  }

  // 根据ID获取检测文本
  async getDetectionTextById(id) {
    try {
      return await DetectionText.findByPk(id);
    } catch (error) {
      throw new Error(`获取检测文本失败: ${error.message}`);
    }
  }

  // 更新检测文本
  async updateDetectionText(id, updateData) {
    try {
      const [updatedRowsCount] = await DetectionText.update(updateData, {
        where: { id }
      });
      
      if (updatedRowsCount === 0) {
        throw new Error('检测文本不存在');
      }
      
      return await this.getDetectionTextById(id);
    } catch (error) {
      throw new Error(`更新检测文本失败: ${error.message}`);
    }
  }

  // 增加使用次数
  async incrementUsageCount(id) {
    try {
      const text = await this.getDetectionTextById(id);
      if (!text) {
        throw new Error('检测文本不存在');
      }

      return await this.updateDetectionText(id, {
        usage_count: text.usage_count + 1
      });
    } catch (error) {
      throw new Error(`增加使用次数失败: ${error.message}`);
    }
  }

  // 删除检测文本
  async deleteDetectionText(id) {
    try {
      const deletedRowsCount = await DetectionText.destroy({
        where: { id }
      });
      
      if (deletedRowsCount === 0) {
        throw new Error('检测文本不存在');
      }
      
      return true;
    } catch (error) {
      throw new Error(`删除检测文本失败: ${error.message}`);
    }
  }

  // 评估记录相关操作

  // 创建评估记录
  async createEvaluationRecord(recordData) {
    try {
      // 增加检测文本使用次数
      if (recordData.text_id) {
        await this.incrementUsageCount(recordData.text_id);
      }
      
      return await EvaluationRecord.create(recordData);
    } catch (error) {
      throw new Error(`创建评估记录失败: ${error.message}`);
    }
  }

  // 获取用户评估记录
  async getUserEvaluationRecords(userId, options = {}) {
    try {
      const { page = 1, limit = 10, startDate, endDate } = options;
      const offset = (page - 1) * limit;
      
      const whereClause = { user_id: userId };
      if (startDate && endDate) {
        whereClause.created_at = {
          [Op.between]: [startDate, endDate]
        };
      }

      const { count, rows } = await EvaluationRecord.findAndCountAll({
        where: whereClause,
        include: [{
          model: DetectionText,
          as: 'detectionText'
        }],
        order: [['created_at', 'DESC']],
        limit: parseInt(limit),
        offset: parseInt(offset)
      });

      return {
        records: rows,
        total: count,
        page: parseInt(page),
        limit: parseInt(limit),
        totalPages: Math.ceil(count / limit)
      };
    } catch (error) {
      throw new Error(`获取用户评估记录失败: ${error.message}`);
    }
  }

  // 根据ID获取评估记录
  async getEvaluationRecordById(id) {
    try {
      return await EvaluationRecord.findByPk(id, {
        include: [{
          model: DetectionText,
          as: 'detectionText'
        }, {
          model: User,
          as: 'user',
          attributes: ['id', 'username', 'nickname', 'avatar']
        }]
      });
    } catch (error) {
      throw new Error(`获取评估记录失败: ${error.message}`);
    }
  }

  // 更新评估记录
  async updateEvaluationRecord(id, updateData) {
    try {
      const [updatedRowsCount] = await EvaluationRecord.update(updateData, {
        where: { id }
      });
      
      if (updatedRowsCount === 0) {
        throw new Error('评估记录不存在');
      }
      
      return await this.getEvaluationRecordById(id);
    } catch (error) {
      throw new Error(`更新评估记录失败: ${error.message}`);
    }
  }

  // 删除评估记录
  async deleteEvaluationRecord(id) {
    try {
      const deletedRowsCount = await EvaluationRecord.destroy({
        where: { id }
      });
      
      if (deletedRowsCount === 0) {
        throw new Error('评估记录不存在');
      }
      
      return true;
    } catch (error) {
      throw new Error(`删除评估记录失败: ${error.message}`);
    }
  }

  // 获取用户评估统计
  async getUserEvaluationStats(userId) {
    try {
      const totalEvaluations = await EvaluationRecord.count({
        where: { user_id: userId }
      });

      const stats = await EvaluationRecord.findOne({
        where: { user_id: userId },
        attributes: [
          [EvaluationRecord.sequelize.fn('AVG', EvaluationRecord.sequelize.col('overall_score')), 'avgScore'],
          [EvaluationRecord.sequelize.fn('MAX', EvaluationRecord.sequelize.col('overall_score')), 'maxScore'],
          [EvaluationRecord.sequelize.fn('AVG', EvaluationRecord.sequelize.col('pronunciation_score')), 'avgPronunciation'],
          [EvaluationRecord.sequelize.fn('AVG', EvaluationRecord.sequelize.col('fluency_score')), 'avgFluency'],
          [EvaluationRecord.sequelize.fn('AVG', EvaluationRecord.sequelize.col('completeness_score')), 'avgCompleteness']
        ],
        raw: true
      });

      return {
        totalEvaluations,
        averageScore: parseFloat(stats?.avgScore || 0).toFixed(2),
        bestScore: parseFloat(stats?.maxScore || 0).toFixed(2),
        averagePronunciation: parseFloat(stats?.avgPronunciation || 0).toFixed(2),
        averageFluency: parseFloat(stats?.avgFluency || 0).toFixed(2),
        averageCompleteness: parseFloat(stats?.avgCompleteness || 0).toFixed(2)
      };
    } catch (error) {
      throw new Error(`获取用户评估统计失败: ${error.message}`);
    }
  }

  // 获取评估排行榜
  async getEvaluationLeaderboard(options = {}) {
    try {
      const { limit = 10, period = 'all' } = options;
      
      let whereClause = {};
      if (period === 'week') {
        const weekAgo = new Date();
        weekAgo.setDate(weekAgo.getDate() - 7);
        whereClause.created_at = { [Op.gte]: weekAgo };
      } else if (period === 'month') {
        const monthAgo = new Date();
        monthAgo.setMonth(monthAgo.getMonth() - 1);
        whereClause.created_at = { [Op.gte]: monthAgo };
      }

      const leaderboard = await EvaluationRecord.findAll({
        where: whereClause,
        attributes: [
          'user_id',
          [EvaluationRecord.sequelize.fn('AVG', EvaluationRecord.sequelize.col('overall_score')), 'avgScore'],
          [EvaluationRecord.sequelize.fn('COUNT', EvaluationRecord.sequelize.col('id')), 'evaluationCount']
        ],
        include: [{
          model: User,
          as: 'user',
          attributes: ['id', 'username', 'nickname', 'avatar']
        }],
        group: ['user_id'],
        order: [[EvaluationRecord.sequelize.literal('avgScore'), 'DESC']],
        limit: parseInt(limit)
      });

      return leaderboard;
    } catch (error) {
      throw new Error(`获取评估排行榜失败: ${error.message}`);
    }
  }
}

module.exports = new DetectionDao();
